package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.ApplicantForm;
import forms.Degree;


public class ApplicationModel {
	
	private static String selectStr = "SELECT country_name FROM countries;";
	private static String selectLoc = "SELECT location_name FROM locations;";
	/*
	private static String selectUniv = "SELECT university_name FROM universities WHERE state =?;";
	private static String countUniv = "SELECT COUNT(university_name) FROM universities u"+
	" WHERE state =?";
	*/
	private static String selectMaj = "SELECT discipline_name FROM disciplines;";
	private static String selectSpec = "SELECT specialization_name FROM specializations;";
	
	private static String insertApp = "INSERT INTO students (first_name, middle_initial, last_name, citizenship, residence, street_address, city, state_code, zip_code, area_code, phone_number, specialization) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
	private static String insertDeg = "INSERT INTO degrees (location_name, university_name, major_name, month, year, gpa, degree_title, transcript_file, student) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	
	
	private static String selectAppInfo = "SELECT stu.first_name, stu.middle_initial, stu.last_name, c.country_name AS citizenship, c.country_name AS residence, stu.street_address, stu.city, stu.state_code, stu.zip_code, stu.area_code, stu.phone_number, sp.specialization_name AS specialization, users.appid"
		+ " FROM students stu, countries c, specializations sp, users"
		+ " WHERE stu.citizenship = c.id"
		+ " AND stu.residence = c.id"
		+ " AND stu.specialization = sp.id"
		+ " AND users.appid = stu.id"
		+ " AND users.appid = ?";
	
	private static String selectDegInfo = "SELECT DISTINCT loc.location_name as location, u.university_name AS university, d.discipline_name AS major, deg.month, deg.year, deg.gpa, deg.degree_title, stu.first_name AS studentf, stu.last_name AS studentl, users.appid"
		+ " FROM students stu, locations loc, universities u, disciplines d, degrees deg, users"
		+ " WHERE stu.id = deg.student"
		+ " AND deg.location_name = loc.id"
		+ " AND deg.university_name = u.id"
		+ " AND deg.major_name = d.id"
		+ " AND users.appid = stu.id"
		+ " AND users.appid = ?";
	
	
	private static String selectSpecAnal = "SELECT specialization_name, count(distinct specialization) as spec_count" +
	" From student_specials ss, specializations" +
	" Where specializations.id = ss.specialization" +
	" GROUP BY specialization_name" +
	" UNION" +
	" SELECT specialization_name, 0 AS count" +
	" FROM specializations" +
	" WHERE NOT EXISTS (SELECT * FROM student_specials ss WHERE specializations.id = ss.specialization)" +
	" ORDER BY spec_count DESC, specialization_name";
	
	private static String selectDiscAnal = "SELECT discipline_name, count(distinct student) as disc_count"+
	" FROM student_degrees sd, disciplines"+
	" WHERE disciplines.id = sd.degree"+
	" GROUP BY discipline_name"+
	" UNION"+
	" SELECT discipline_name, 0 AS count"+
	" FROM disciplines"+
	" WHERE NOT EXISTS("+
		"SELECT * FROM  student_degrees sd WHERE disciplines.id = sd.degree)"+
	" ORDER BY disc_count desc, discipline_name;";
	
	
	private static String selectAppPage = "SELECT first_name, middle_initial, last_name FROM students;";
	
	
	private static String selectSpecAppPage = "SELECT DISTINCT app.application, first_name, middle_initial, last_name, ARP FROM student_specials ss, students stu, specializations sp, applications app"
    	+ " WHERE  sp.specialization_name = ?"+
    	" AND ss.specialization = sp.id"+
    	" AND stu.id = ss.student" +
    	" AND app.application = stu.id;";
	
	private static String selectDiscAppPage = "SELECT DISTINCT app.application, first_name, middle_initial, last_name, ARP FROM student_degrees sd, students stu, disciplines d, applications app"
    	+ " WHERE d.discipline_name =?" +
    	" AND sd.degree = d.id" +
    	" AND stu.id = sd.student" +
    	" AND app.application = stu.id;";
	
	
	//////////////////////////////////////////////////////////////////////////////////////////
	
	
	public static CachedRowSet getAllCountries() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectStr);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allCountries = pStmt.executeQuery();

			CachedRowSet crsCountries = new CachedRowSetImpl();
			crsCountries.populate(allCountries);

			allCountries.close();
			pStmt.close();
			conn.close();

			return crsCountries;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getAllLocations() throws DbException {
		
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectLoc);

			// Use the created statement to SELECT
			// the location_name's FROM the locations table
			ResultSet allLocations = pStmt.executeQuery();

			CachedRowSet crsLocations = new CachedRowSetImpl();
			crsLocations.populate(allLocations);

			allLocations.close();
			allLocations = null;
			
			pStmt.close();
			pStmt = null;
			
			conn.close();

			return crsLocations;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	
	
	public static CachedRowSet getLocationsUniversities(String currentLocationName) throws DbException {

		PreparedStatement pStmt = null;
		
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			pStmt = conn.prepareStatement("SELECT university_name FROM universities WHERE state ='" + currentLocationName + "';");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet locationsUniversities = pStmt.executeQuery();
			
	
			

			CachedRowSet crsUniversities = new CachedRowSetImpl();
			crsUniversities.populate(locationsUniversities);

			locationsUniversities.close();
			pStmt.close();
			conn.close();

			return crsUniversities;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		finally{
			if(pStmt != null){
				try{
					pStmt.close();
				}catch (SQLException e){}
				pStmt = null;
			}
		}
	}
	
	public static CachedRowSet getUnivCountOfLocation(String currentLocation) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement("SELECT COUNT(university_name) FROM universities u"+
					" WHERE state ='"+currentLocation+"'");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet countUniversities = pStmt.executeQuery();

			CachedRowSet crsCountUniv = new CachedRowSetImpl();
			crsCountUniv.populate(countUniversities);

			countUniversities.close();
			pStmt.close();
			conn.close();

			return crsCountUniv;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	
	public static CachedRowSet getAllMajors() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectMaj);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allMajors = pStmt.executeQuery();

			CachedRowSet crsMajors = new CachedRowSetImpl();
			crsMajors.populate(allMajors);

			allMajors.close();
			pStmt.close();
			conn.close();

			return crsMajors;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static CachedRowSet getAllSpecializations() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectSpec);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allSpecializations = pStmt.executeQuery();

			CachedRowSet crsSpecializations = new CachedRowSetImpl();
			crsSpecializations.populate(allSpecializations);

			allSpecializations.close();
			pStmt.close();
			conn.close();

			return crsSpecializations;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	
	/////////////////////////////////////////////////////////
	
	
	
	
	public static int insertApplicant(ApplicantForm student)
	throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
		
			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			PreparedStatement pStmt = conn.prepareStatement(insertApp);
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery("SELECT * FROM countries WHERE country_name='"+student.getCountry1()+"';");
			rs.next();
			Statement statement2 = conn.createStatement();
			ResultSet rs2 = statement2.executeQuery("SELECT * FROM countries WHERE country_name='"+student.getCountry2()+"';");
			rs2.next();
			Statement statement3 = conn.createStatement();
			ResultSet rs3 = statement3.executeQuery("SELECT * FROM specializations WHERE specialization_name='"+student.getSpecialization()+"';");
			rs3.next();
			Statement statement4 = conn.createStatement();
		
			pStmt.setString(1, student.getFirstname());
			pStmt.setString(2, student.getMiddlename());
			pStmt.setString(3, student.getLastname());
			pStmt.setInt(4, rs.getInt("id"));
			pStmt.setInt(5, rs2.getInt("id"));
			pStmt.setString(6, student.getAddress());
			pStmt.setString(7, student.getCity());
            if (student.getState() == null)  {
            	pStmt.setString(8, student.getCountrycode());
            }
            else  {
            	pStmt.setString(8, student.getState());
            }
			pStmt.setInt(9, Integer.parseInt(student.getZipcode()));
			pStmt.setInt(10, Integer.parseInt(student.getAreacode()));
			pStmt.setInt(11, Integer.parseInt(student.getTelephonenumber()));
			pStmt.setInt(12, rs3.getInt("id"));
		
			pStmt.executeUpdate();
			
			
			ResultSet rs4 = statement4.executeQuery("SELECT id FROM students WHERE first_name='" + student.getFirstname() + "' AND last_name='" + student.getLastname() + "';");
			rs4.next();
			int appID = rs4.getInt("id");
		
			// Commit transaction
			conn.commit();
			
			rs.close();
			rs2.close();
			rs3.close();
			rs4.close();
			
			statement.close();
			statement2.close();
			statement3.close();
			statement4.close();
			
			pStmt.close();
			conn.close();
			
			return appID;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	
	public static void insertOtherMajor(String otherMajor)
	throws DbException {
		PreparedStatement pStmt= null;
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			Statement statement2 = conn.createStatement();
			int student_id = 0;

			// Create the statement
			pStmt = conn.prepareStatement("INSERT INTO disciplines (discipline_name) VALUES ('" + otherMajor + "')");
			pStmt.executeUpdate();
			
			
			ResultSet rs = statement.executeQuery("SELECT * FROM disciplines WHERE discipline_name='"+otherMajor+"'");
			ResultSet rs2 = statement2.executeQuery("SELECT * FROM students");
    		while (rs2.next())
    		{
    			student_id = rs2.getInt("id");
    		}
			rs.next();
			pStmt = conn
            .prepareStatement("INSERT INTO student_degrees (student, degree) VALUES (?,?)");
			pStmt.setInt(1, student_id);
			pStmt.setInt (2, rs.getInt("id"));
			pStmt.executeUpdate();  
			// otherMajor = false;
			
			
			conn.commit();
			pStmt.close();
			
			
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		finally{
			if(pStmt != null){
				try{
					pStmt.close();
				}catch (SQLException e){}
				pStmt = null;
			}
		}
	}
	
	
	
	
	public static void insertDegree(ApplicantForm student, Integer currentCounter, List<Degree> degList)
	throws DbException, FileNotFoundException, IOException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			List<Degree> degreeList = degList;
			PreparedStatement pStmt = conn.prepareStatement(insertDeg);
			ResultSet rs=null, rs2=null, rs3=null, rs4 = null;
			Statement statement = conn.createStatement(), statement2 = conn.createStatement(), statement3 = conn.createStatement(), statement4 = conn.createStatement();
			for (int i =0; i <= currentCounter; i++){
				
				Degree currentDegree = degreeList.get(i);
				
				
				
				
				rs = statement.executeQuery("SELECT * FROM locations WHERE location_name='"+currentDegree.getLocation()+"';");
				rs.next();
				
				rs2 = statement2.executeQuery("SELECT * FROM universities WHERE university_name='"+currentDegree.getUniversity()+"';");
				rs2.next();
				
				rs3 = statement3.executeQuery("SELECT * FROM disciplines WHERE discipline_name='"+currentDegree.getMajor()+"';");
				rs3.next();
				
				rs4 = statement4.executeQuery("SELECT * FROM students WHERE first_name='"+student.getFirstname()+"' AND last_name='"+student.getLastname()+"';");
				rs4.next();
			
				pStmt.setInt(1, rs.getInt("id"));
				pStmt.setInt(2, rs2.getInt("id"));
				pStmt.setInt(3, rs3.getInt("id"));
				pStmt.setInt(4, Integer.parseInt(currentDegree.getMonth()));
				pStmt.setInt(5, Integer.parseInt(currentDegree.getYear()));
				pStmt.setDouble(6, Double.parseDouble(currentDegree.getGPA()));
				pStmt.setString(7, currentDegree.getDegreeTitle());
				pStmt.setBytes(8, currentDegree.getMyFile().getFileData());
				//pStmt.setBinaryStream(8, currentDegree.getMyFile().getInputStream(), currentDegree.getMyFile().getFileSize());
				pStmt.setInt(9, rs4.getInt("id"));
				
			
				pStmt.executeUpdate();
				
				rs.close();
				rs2.close();
				rs3.close();
				rs4.close();
			}
			// Commit transaction
			conn.commit();
		
			
			
			statement.close();
			statement2.close();
			statement3.close();
			statement4.close();
			
			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static void insertStudentDeg (List<Degree> degreeList, Integer degreeCounter)throws DbException  {
		ResultSet rs=null;
		int student_id = 0;
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			Statement statement2 = conn.createStatement();
			PreparedStatement pStmt = null;
			ResultSet rs2 = statement2.executeQuery("SELECT * FROM students");
			
			while(rs2.next()){
            	student_id = rs2.getInt("id"); 
            }
			
			
			for (int i = 0; i <= degreeCounter; i++)
			{ 
				Degree currentDegree = degreeList.get(i); 
				String currentMajorName = currentDegree.getMajor();
				
				rs = statement.executeQuery("SELECT * FROM disciplines"+
		        		" WHERE discipline_name = '" + currentMajorName +
						"';");
				rs.next();
				pStmt = conn
                .prepareStatement("INSERT INTO student_degrees (student, degree) VALUES (?,?)");
    			pStmt.setInt(1, student_id);
    			pStmt.setInt (2, rs.getInt("id"));
    			pStmt.executeUpdate();  
			}
			
			pStmt.executeUpdate();  
			
			rs.close();
			rs2.close();
			
			conn.commit();		
			
			statement.close();
			statement2.close();
			
			pStmt.close();
			conn.close();
		}
		catch (SQLException ex) {
			throw new DbException(ex);
		}
		catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	
	public static void insertStudentSpec (String currentSpecialization)throws DbException  {
		ResultSet rs=null, rs2=null;
		int student_id = 0;
		int special_id = 0;
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement statement = conn.createStatement();
			Statement statement2 = conn.createStatement();
			
	        rs = statement.executeQuery("SELECT * FROM specializations WHERE specialization_name='"+currentSpecialization+"';");
	        rs2 = statement2.executeQuery("SELECT * FROM students");
	        
	        rs.next();
	        while(rs2.next()){
	        	student_id = rs2.getInt("id"); 
	        }
	        special_id = rs.getInt("id");
	        PreparedStatement pStmt = conn
	        .prepareStatement("INSERT INTO student_specials (student, specialization) VALUES (?,?)");
	        pStmt.setInt (1, student_id);
			pStmt.setInt (2, special_id);
			
			pStmt.executeUpdate();  
			
			rs.close();
			rs2.close();
			
			conn.commit();		
			
			statement.close();
			statement2.close();
			
			pStmt.close();
			conn.close();
		}
		catch (SQLException ex) {
			throw new DbException(ex);
		}
		catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	
	//////////////////////////////////////////////////////////////////////////////////////////////
	
	

	
	
	
	public static String getAppStatus(String currentUserName)
	throws DbException {

		ResultSet rs = null;
		
		try {
			Connection conn = DbConnectionPool.getConnection();
		
			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			Statement statement = conn.createStatement();
			rs = statement.executeQuery("SELECT * FROM users WHERE user_name='" + currentUserName + "';");
			rs.next();
		
			String status = rs.getString("status");
			
		
			// Commit transaction
			conn.commit();
			
			rs.close();
			rs = null;
			
			statement.close();
			
			conn.close();
			
			return status;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		finally{
			if(rs != null){
				try{
					rs.close();
				}catch (SQLException e){}
				rs = null;
			}
		}
	}
	
	public static int getAppid(String currentUserName)
	throws DbException {

		ResultSet rs = null;
		
		try {
			Connection conn = DbConnectionPool.getConnection();
		
			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			Statement statement = conn.createStatement();
			rs = statement.executeQuery("SELECT * FROM users WHERE user_name='" + currentUserName + "';");
			rs.next();
		
			int appid = rs.getInt("appid");
			
		
			// Commit transaction
			conn.commit();
			
			rs.close();
			rs = null;
			
			statement.close();
			
			conn.close();
			
			return appid;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		finally{
			if(rs != null){
				try{
					rs.close();
				}catch (SQLException e){}
				rs = null;
			}
		}
	}
	
	
	
	
	
	
	
	public static void updateAppStatus(String currentUser, int currentAppID)
	throws DbException {
		PreparedStatement pStmt= null;
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			pStmt = conn.prepareStatement("UPDATE users SET status='submitted', appid='" + currentAppID + "' WHERE user_name = '" + currentUser + "';");

			pStmt.executeUpdate();
			conn.commit();
			pStmt.close();
			
			
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		finally{
			if(pStmt != null){
				try{
					pStmt.close();
				}catch (SQLException e){}
				pStmt = null;
			}
		}
	}
	
	
	
	
	
	
	
	
	public static CachedRowSet getApplicantInfoFor(int currentAppID) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAppInfo);
			pStmt.setInt(1, currentAppID);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet appInfo = pStmt.executeQuery();

			CachedRowSet crsAppInfo = new CachedRowSetImpl();
			crsAppInfo.populate(appInfo);

			appInfo.close();
			pStmt.close();
			conn.close();

			return crsAppInfo;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getDegreeInfoFor(int currentAppID) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDegInfo);
			pStmt.setInt(1, currentAppID);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet degInfo = pStmt.executeQuery();

			CachedRowSet crsDegInfo = new CachedRowSetImpl();
			crsDegInfo.populate(degInfo);

			degInfo.close();
			pStmt.close();
			conn.close();

			return crsDegInfo;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	/////////////////////////////////////
	
	
	
	public static CachedRowSet getSpecAnal() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectSpecAnal);
			// pStmt.setInt(1, currentAppID);

			ResultSet specAnal = pStmt.executeQuery();

			CachedRowSet crsSpecAnal = new CachedRowSetImpl();
			crsSpecAnal.populate(specAnal);

			specAnal.close();
			pStmt.close();
			conn.close();

			return crsSpecAnal;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getDiscAnal() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDiscAnal);
			// pStmt.setInt(1, currentAppID);

			ResultSet discAnal = pStmt.executeQuery();

			CachedRowSet crsDiscAnal = new CachedRowSetImpl();
			crsDiscAnal.populate(discAnal);

			discAnal.close();
			pStmt.close();
			conn.close();

			return crsDiscAnal;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static CachedRowSet getAppPage() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAppPage);
			// pStmt.setString(1, currentSpecialization);

			ResultSet appPage = pStmt.executeQuery();

			CachedRowSet crsAppPage = new CachedRowSetImpl();
			crsAppPage.populate(appPage);

			appPage.close();
			pStmt.close();
			conn.close();

			return crsAppPage;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getSpecAppPage(String currentSpecialization) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectSpecAppPage);
			pStmt.setString(1, currentSpecialization);

			ResultSet specAppPage = pStmt.executeQuery();

			CachedRowSet crsSpecAppPage = new CachedRowSetImpl();
			crsSpecAppPage.populate(specAppPage);

			specAppPage.close();
			pStmt.close();
			conn.close();

			return crsSpecAppPage;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getDiscAppPage(String currentDiscipline) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDiscAppPage);
			pStmt.setString(1, currentDiscipline);

			ResultSet discAppPage = pStmt.executeQuery();

			CachedRowSet crsDiscAppPage = new CachedRowSetImpl();
			crsDiscAppPage.populate(discAppPage);

			discAppPage.close();
			pStmt.close();
			conn.close();

			return crsDiscAppPage;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
}
